from openpyxl.styles import PatternFill, Alignment
from openpyxl import Workbook
from openpyxl import load_workbook
import random
import xlwings as xw
from openpyxl.styles import Font

# +++++++++++++++++++++++ 创建一个Excel文件，填充黄色并保存 ++++++++++++++++++++++
# 创建一个新的Excel文件
wb = Workbook()
# 获取当前正在操作的工作表
ws = wb.active

# 表格填充黄色
ws['A1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['B1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['C1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['D1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['F1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['G1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['H1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 将工作簿保存到磁盘上
wb.save('D:/桌面/Drug.xlsx')
print('Excel创建成功！')

# ++++++++++++++++++++++++++++++++++++ 编写Excel文件的内容 +++++++++++++++++++++++++++++++++++++
StringBase = '\u7684\u4e00\u4e86\u662f\u6211\u4e0d\u5728\u4eba\u4eec\u6709\u6765\u4ed6\u8fd9\u4e0a\u7740\u4e2a\u5730' \
             '\u5230\u5927\u91cc\u8bf4\u5c31\u53bb\u5b50\u5f97\u4e5f\u548c\u90a3\u8981\u4e0b\u770b\u5929\u65f6\u8fc7' \
             '\u51fa\u5c0f\u4e48\u8d77\u4f60\u90fd\u628a\u597d\u8fd8\u591a\u6ca1\u4e3a\u53c8\u53ef\u5bb6\u5b66\u53ea' \
             '\u4ee5\u4e3b\u4f1a\u6837\u5e74\u60f3\u751f\u540c\u8001\u4e2d\u5341\u4ece\u81ea\u9762\u524d\u5934\u9053' \
             '\u5b83\u540e\u7136\u8d70\u5f88\u50cf\u89c1\u4e24\u7528\u5979\u56fd\u52a8\u8fdb\u6210\u56de\u4ec0\u8fb9' \
             '\u4f5c\u5bf9\u5f00\u800c\u5df1\u4e9b\u73b0\u5c71\u6c11\u5019\u7ecf\u53d1\u5de5\u5411\u4e8b\u547d\u7ed9' \
             '\u957f\u6c34\u51e0\u4e49\u4e09\u58f0\u4e8e\u9ad8\u624b\u77e5\u7406\u773c\u5fd7\u70b9\u5fc3\u6218\u4e8c' \
             '\u95ee\u4f46\u8eab\u65b9\u5b9e\u5403\u505a\u53eb\u5f53\u4f4f\u542c\u9769\u6253\u5462\u771f\u5168\u624d' \
             '\u56db\u5df2\u6240\u654c\u4e4b\u6700\u5149\u4ea7\u60c5\u8def\u5206\u603b\u6761\u767d\u8bdd\u4e1c\u5e2d' \
             '\u6b21\u4eb2\u5982\u88ab\u82b1\u53e3\u653e\u513f\u5e38\u6c14\u4e94\u7b2c\u4f7f\u5199\u519b\u5427\u6587' \
             '\u8fd0\u518d\u679c\u600e\u5b9a\u8bb8\u5feb\u660e\u884c\u56e0\u522b\u98de\u5916\u6811\u7269\u6d3b\u90e8' \
             '\u95e8\u65e0\u5f80\u8239\u671b\u65b0\u5e26\u961f\u5148\u529b\u5b8c\u5374\u7ad9\u4ee3\u5458\u673a\u66f4' \
             '\u4e5d\u60a8\u6bcf\u98ce\u7ea7\u8ddf\u7b11\u554a\u5b69\u4e07\u5c11\u76f4\u610f\u591c\u6bd4\u9636\u8fde' \
             '\u8f66\u91cd\u4fbf\u6597\u9a6c\u54ea\u5316\u592a\u6307\u53d8\u793e\u4f3c\u58eb\u8005\u5e72\u77f3\u6ee1' \
             '\u65e5\u51b3\u767e\u539f\u62ff\u7fa4\u7a76\u5404\u516d\u672c\u601d\u89e3\u7acb\u6cb3\u6751\u516b\u96be' \
             '\u65e9\u8bba\u5417\u6839\u5171\u8ba9\u76f8\u7814\u4eca\u5176\u4e66\u5750\u63a5\u5e94\u5173\u4fe1\u89c9' \
             '\u6b65\u53cd\u5904\u8bb0\u5c06\u5343\u627e\u4e89\u9886\u6216\u5e08\u7ed3\u5757\u8dd1\u8c01\u8349\u8d8a' \
             '\u5b57\u52a0\u811a\u7d27\u7231\u7b49\u4e60\u9635\u6015\u6708\u9752\u534a\u706b\u6cd5\u9898\u5efa\u8d76' \
             '\u4f4d\u5531\u6d77\u4e03\u5973\u4efb\u4ef6\u611f\u51c6\u5f20\u56e2\u5c4b\u79bb\u8272\u8138\u7247\u79d1' \
             '\u5012\u775b\u5229\u4e16\u521a\u4e14\u7531\u9001\u5207\u661f\u5bfc\u665a\u8868\u591f\u6574\u8ba4\u54cd' \
             '\u96ea\u6d41\u672a\u573a\u8be5\u5e76\u5e95\u6df1\u523b\u5e73\u4f1f\u5fd9\u63d0\u786e\u8fd1\u4eae\u8f7b' \
             '\u8bb2\u519c\u53e4\u9ed1\u544a\u754c\u62c9\u540d\u5440\u571f\u6e05\u9633\u7167\u529e\u53f2\u6539\u5386' \
             '\u8f6c\u753b\u9020\u5634\u6b64\u6cbb\u5317\u5fc5\u670d\u96e8\u7a7f\u5185\u8bc6\u9a8c\u4f20\u4e1a\u83dc' \
             '\u722c\u7761\u5174\u5f62\u91cf\u54b1\u89c2\u82e6\u4f53\u4f17\u901a\u51b2\u5408\u7834\u53cb\u5ea6\u672f' \
             '\u996d\u516c\u65c1\u623f\u6781\u5357\u67aa\u8bfb\u6c99\u5c81\u7ebf\u91ce\u575a\u7a7a\u6536\u7b97\u81f3' \
             '\u653f\u57ce\u52b3\u843d\u94b1\u7279\u56f4\u5f1f\u80dc\u6559\u70ed\u5c55\u5305\u6b4c\u7c7b\u6e10\u5f3a' \
             '\u6570\u4e61\u547c\u6027\u97f3\u7b54\u54e5\u9645\u65e7\u795e\u5ea7\u7ae0\u5e2e\u5566\u53d7\u7cfb\u4ee4' \
             '\u8df3\u975e\u4f55\u725b\u53d6\u5165\u5cb8\u6562\u6389\u5ffd\u79cd\u88c5\u9876\u6025\u6797\u505c\u606f' \
             '\u53e5\u533a\u8863\u822c\u62a5\u53f6\u538b\u6162\u53d4\u80cc\u7ec6'

# 打开Excel
wb = load_workbook('D:/桌面/Drug.xlsx')


# 编号
def GetNumber():
    sj1 = random.choice(('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'P', 'S', 'T'))
    sj2 = random.randint(100000, 9999999)
    sz = str(sj1) + str(sj2)
    return sz


# 价格
def getPrice():
    return str(random.randint(1, 999))


# 产地
def getAddress():
    return random.choice(('北京市', '天津市', '上海市', '重庆市', '河北省', '山西省', '辽宁省', '吉林省', '黑龙江省',
                          '江苏省', '浙江省', '安徽省', '福建省', '江西省', '山东省', '河南省', '湖北省', '湖南省',
                          '广东省', '海南省', '四川省', '贵州省', '云南省', '陕西省', '甘肃省', '青海省', '台湾省',
                          '内蒙古自治区', '广西壮族自治区', '西藏自治区', '宁夏回族自治区', '新疆维吾尔自治区',
                          '香港特别行政区', '澳门特别行政区'))

# 厂家
def getProducer():
    sj = random.choice(('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'P', 'S', 'T'))
    sj1 = random.choice(('红桃', '黑桃', '方块', '梅花'))
    sj2 = '制药厂'
    sz = str(sj1) + str(sj) + str(sj2)
    return sz
# 类型
def getTypes():
    return random.choice(('中药', '西药', '中成药'))


# 包装单位
def getPacking():
    return random.choice(('盒', '袋', '瓶'))


# 单位
def getUnit():
    return random.choice(('片', '粒', '丸', '支'))


# 单位
def getDose():
    return random.choice(('g', 'mg'))


def getNameOrAddress(flag):
    '''flag=1表示返回随机药名，flag=0表示返回随机地址'''
    sj1 = random.choice(('药', '丸', '丹', '片'))
    result = ''
    if flag == 1:
        rangestart, rangeend = 2, 5
        ##姓名
    elif flag == 0:
        rangestart, rangeend = 5, 10
    else:
        print('flag must be 1 or 0')
    for i in range(random.randrange(rangestart, rangeend)):
        result += random.choice(StringBase)
    sz = result + str(sj1)
    return sz


# 加载excel，注意路径要与脚本一致
# 激活excel表
sheet = wb.active
# 向excel中写入表头
sheet['a1'] = '编号'
sheet['b1'] = '药名'
sheet['c1'] = '包装单位'
sheet['d1'] = '单位'
sheet['e1'] = '剂量单位'
sheet['f1'] = '售价'
sheet['g1'] = '产地'
sheet['h1'] = '生产厂家'
sheet['i1'] = '类型'
for i in range(1999):
    number = GetNumber()
    name = getNameOrAddress(1)
    Packing = getPacking()
    Unit = getUnit()
    Dose = getDose()
    Price = getPrice()
    address = getAddress()
    Manufacturer = getProducer()
    Types = getTypes()
    sheet.append([number, name, Packing, Unit, Dose, Price, address, Manufacturer, Types])

# 设置行高和列宽
for n in range(1, 10010):
    sheet.row_dimensions[n].height = 20
    sheet.column_dimensions["A"].width = 15
    sheet.column_dimensions["B"].width = 15
    sheet.column_dimensions["C"].width = 10
    sheet.column_dimensions["D"].width = 10
    sheet.column_dimensions["E"].width = 10
    sheet.column_dimensions["F"].width = 10
    sheet.column_dimensions["G"].width = 20
    sheet.column_dimensions["H"].width = 20
    sheet.column_dimensions["I"].width = 10

# ++++++++++++++++++++++++++++++++++++++++++++++++++++++  文字居中  ++++++++++++++++++++++
# 获取所有的sheet名
sheets = wb.sheetnames

# 读取第一个sheet表格
ws = wb[sheets[0]]
alignment_center = Alignment(horizontal='center', vertical='center')

# 指定区域单元格居中
ws_area = ws["A1:I10010"]
for i in ws_area:
    for j in i:
        j.alignment = alignment_center

# +++++++++++++++++++++++++++++++++++++++++++++++++++++  首行字体加粗  ++++++++++++++++++++++

# 获取所有的sheet名
sheets = wb.sheetnames

# 读取第一个sheet表格
ws = wb[sheets[0]]
font1 = Font(name="楷体")
font2 = Font(name="楷体", bold=True)

# 指定区域单元格字体
ws_area = ws["A1:I1"]
k = 0
for i in ws_area:
    k = k + 1
    for j in i:
        if k == 1:
            j.font = font2
        else:
            j.font = font1

wb.save('D:/桌面/Drug.xlsx')  # 保存表格 加路径
print('数据写入成功！')

# ++++++++++++++++++++++++++++++++ 表格加边框 +++++++++++++++++++++++++
# 打开存好的 Excel
app = xw.App()  # 设置应用
wb = xw.Book('D:/桌面/Drug.xlsx')  # 打开文件
ws = wb.sheets['Sheet']  # 选择表格

# 获取表格行列
last_column = ws.range(1, 1).end('right').get_address(0, 0)[0]  # 获取最后一列
last_row = ws.range(1, 1).end('down').row  # 获取最后一行
a_range = f'A1:{last_column}{last_row}'  # 生成表格的数据范围

# 设置边框
ws.range(a_range).api.Borders(8).LineStyle = 1  # 上边框
ws.range(a_range).api.Borders(9).LineStyle = 1  # 下边框
ws.range(a_range).api.Borders(7).LineStyle = 1  # 左边框
ws.range(a_range).api.Borders(10).LineStyle = 1  # 右边框
ws.range(a_range).api.Borders(12).LineStyle = 1  # 内横边框
ws.range(a_range).api.Borders(11).LineStyle = 1  # 内纵边框

# 保存并关闭 Excel
wb.save('D:/桌面/Drug.xlsx')
# wb.close()
# app.quit()
